package com.common.weibolib;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.common.weibolib.NdColumns.FlowerMsgColumns;
import com.common.weibolib.NdColumns.IdolListColumns;
import com.common.weibolib.NdColumns.PraiseColumns;
import com.common.weibolib.NdColumns.PraiseUserColumns;
import com.common.weibolib.NdColumns.ReplyColumns;
import com.common.weibolib.NdColumns.SinaBindColumns;
import com.common.weibolib.NdColumns.TimeStampColumns;
import com.common.weibolib.NdColumns.TopicInfoColumns;
import com.common.weibolib.NdColumns.UserColumns;
import com.common.weibolib.NdColumns.UserLastLocationColumns;
import com.common.weibolib.NdColumns.UserLastWeatherColumns;
import com.common.weibolib.NdColumns.UserTopicIncludeNamesColumns;

public class NdWeiboDataHelper extends SQLiteOpenHelper {

	public static final String DATABASE_NAME_SUFFIX = "ndweibo";
	
	public static final String PRE_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS ";
	public static final String PRE_CREATE_INDEX = "CREATE INDEX IF NOT EXISTS ";
	
	public static final String PUBLIC_TOPIC_TABLE = "public_topic"; // 公共微博列表
	public static final String FRIEND_TOPIC_TABLE = "friend_topic"; // 关注微博列表
	public static final String USER_TOPIC_TABLE = "user_topic"; // 用户微博列表（每个用户缓存最多20条）
	public static final String AT_TOPIC_TABLE = "at_topic"; // @我的微博列表
	public static final String MY_PRAISED_TOPIC_TABLE = "my_praised_topic"; // 我的被赞微博列表
	public static final String RE_TOPIC_TABLE = "repost_topic"; // 转发微博列表
	public static final String TAG_TOPIC_TABLE = "tag_topic"; // 话题微博列表
	
	public static final String PRAISE_USER_TABLE = "praise_user"; // 赞某条微博的用户列表
	
	public static final String REPLY_TABLE = "reply"; // 评论列表
	public static final String REPLY_ME_TABLE = "reply_me"; // 回复我的评论列表
	public static final String AT_REPLY_TABLE = "at_reply"; // @我的评论列表
	
	public static final String FLOWER_MSG_TABLE = "flower_msg";//鲜花留言
	public static final String IDOL_LIST_TABLE = "idol_list";//关注表
	public static final String SINA_BIND_TABLE = "sina_bind";// 新浪绑定表
	public static final String USER_TABLE = "user"; // 用户表
	public static final String TIMESTAMP_TABLE = "time_stamp"; //时间戳表
	
	 //一个用户的微博列表(包含评论和赞信息,目前只需要评论信息，如果今后需要赞信息，要加数据库的赞字段)
	public static final String USER_TOPIC_INCLUDE_NAME_TABLE = "user_topic_include_name";
	
	public static final String USER_LAST_LOCATION_TABLE = "user_last_location";//用户最后一次定位信息表
	public static final String USER_LAST_WEATHER_TABLE = "user_last_weather";//最后一次天气信息表

	
	public static final String REPLY_COMMON = 
			ReplyColumns.ID + " INTEGER PRIMARY KEY NOT NULL,"
			+ ReplyColumns.CONTENT + " TEXT,"
			+ ReplyColumns.POST_TIME + " INTEGER,"
			+ ReplyColumns.FROM_STRING + " TEXT,"
			+ ReplyColumns.UID + " INTEGER,"
			+ ReplyColumns.TO_RID + " INTEGER,"
			+ ReplyColumns.TO_TID + " INTEGER,"
			+ ReplyColumns.USER + " TEXT,"
			+ ReplyColumns.TO_REPLY + " TEXT,"
			+ ReplyColumns.ROOT_TOPIC + " TEXT";
	
	public static final String TOPIC_COMMON = 
			TopicInfoColumns.ID + " INTEGER PRIMARY KEY NOT NULL,"
			+ TopicInfoColumns.UNIT_ID + " INTEGER,"
			+ TopicInfoColumns.SINA_ID + " INTEGER,"
			+ TopicInfoColumns.FORWARDS + " INTEGER,"
			+ TopicInfoColumns.GROUP_ID + " INTEGER,"
			+ TopicInfoColumns.ROOT_TID + " INTEGER,"
			+ TopicInfoColumns.PRAISED + " NUMERIC,"
			+ TopicInfoColumns.PRAISES + " INTEGER,"
			+ TopicInfoColumns.GLANCES + " INTEGER,"
			+ TopicInfoColumns.REPLYS + " INTEGER,"
			+ TopicInfoColumns.POST_TIME + " INTEGER,"
			+ TopicInfoColumns.CATEGORY + " TEXT,"
			+ TopicInfoColumns.CONTENT + " TEXT,"
			+ TopicInfoColumns.ARTICLE + " TEXT,"
			+ TopicInfoColumns.FROM_STRING + " TEXT,"
			+ TopicInfoColumns.GEO + " TEXT,"
			+ TopicInfoColumns.ATTACH + " TEXT,"
			+ TopicInfoColumns.IMAGE + " TEXT,"
			+ TopicInfoColumns.ROOT_TOPIC + " TEXT,"
			+ TopicInfoColumns.USER + " TEXT,"
			+ TopicInfoColumns.TYPE + " TEXT";

	// 公共微博表
	private static final String CREATE_PUBLIC_TOPIC_TABLE = PRE_CREATE_TABLE + PUBLIC_TOPIC_TABLE 
			+ "("
			+ TOPIC_COMMON 
			+")";
	
	// 公共微博表
	private static final String CREATE_FRIEND_TOPIC_TABLE = PRE_CREATE_TABLE + FRIEND_TOPIC_TABLE
			+ "("
			+ TOPIC_COMMON 
			+")";
	
	// 用户微博表
	private static final String CREATE_USER_TOPIC_TABLE = PRE_CREATE_TABLE + USER_TOPIC_TABLE 
			+ "("
			+ TOPIC_COMMON 
			+ "," + TopicInfoColumns.UID + " INTEGER"
			+")";
			
	// 转发微博表
	private static final String CREATE_RE_TOPIC_TABLE = PRE_CREATE_TABLE + RE_TOPIC_TABLE 
			+ "("
			+ TOPIC_COMMON 
			+")";
	
	// 我的被赞微博表
	private static final String CREATE_MY_PRAISED_TOPIC_TABLE = PRE_CREATE_TABLE + MY_PRAISED_TOPIC_TABLE 
			+ "("
			+ PraiseColumns.ID + " INTEGER PRIMARY KEY NOT NULL,"
			+ PraiseColumns.PRAISE_TIME + " INTEGER,"
			+ PraiseColumns.USER + " TEXT,"
			+ PraiseColumns.TOPIC_INFO + " TEXT"
			+")";
			
	// 赞某条微博的用户表
	public final StringBuilder CREATE_PRAISE_USER_TABLE = new StringBuilder()
			.append(PRE_CREATE_TABLE)
			.append(PRAISE_USER_TABLE)
			.append("(")
			.append(PraiseUserColumns.ID).append(" INTEGER PRIMARY KEY NOT NULL,")
			.append(PraiseUserColumns.PRAISE_TIME).append( " INTEGER,")
			.append(PraiseUserColumns.USER) .append( " TEXT,")
			.append(PraiseUserColumns.TWEET_ID).append( " INTEGER")
			.append(")");

	// @我的微博表
	private static final String CREATE_AT_TOPIC_TABLE = PRE_CREATE_TABLE + AT_TOPIC_TABLE 
			+ "("
			+ TOPIC_COMMON 
			+ "," + TopicInfoColumns.REQUEST_ID + " INTEGER"
			+")";
	
	// @我的评论表
	private static final String CREATE_AT_REPLY_TABLE = PRE_CREATE_TABLE + AT_REPLY_TABLE 
			+ "("
			+ REPLY_COMMON 
			+ "," + ReplyColumns.REQUEST_ID + " INTEGER"
			+")";
	
	// 话题微博
	private static final String CREATE_TAG_TOPIC_TABLE = PRE_CREATE_TABLE + TAG_TOPIC_TABLE 
			+ "("
			+ TOPIC_COMMON 
			+ ","+ TopicInfoColumns.KEY_WORD + " TEXT"
			+")";
	
	// 创建评论表
	public final StringBuilder CREATE_REPLY_TABLE = new StringBuilder()
			.append(PRE_CREATE_TABLE)
			.append(REPLY_TABLE)
			.append("(")
			.append(NdWeiboDataHelper.REPLY_COMMON)
			.append(")");
	
	// 创建对我的微博评论表
	private static final String CREATE_REPLY_ME_TABLE = PRE_CREATE_TABLE + REPLY_ME_TABLE 
			+ "("
			+ REPLY_COMMON 
			+")";

	// 用户
	private static final String CREATE_USER_TABLE = PRE_CREATE_TABLE + USER_TABLE 
			+ "("
			+ UserColumns.ID + " INTEGER PRIMARY KEY NOT NULL, " 
			+ UserColumns.NAME + " TEXT, "
			+ UserColumns.GENDER + " NUMERIC"
			+ ");";

	// 关注表
	private static final String CREATE_IDOL_LIST_TABLE = PRE_CREATE_TABLE + IDOL_LIST_TABLE
			+ " ("
			+ IdolListColumns.UID + " INTEGER KEY NOT NULL,"
			+ IdolListColumns.NICKNAME + " TEXT,"
			+ IdolListColumns.ID + " INTEGER,"
			+ IdolListColumns.IS_FOLLOWING + " INTEGER,"
			+ IdolListColumns.FANS + " INTEGER ," 
			+ IdolListColumns.IDOLS + " INTEGER ," 
			+ IdolListColumns.SINA_UID + " INTEGER,"
			+ " primary key("
			+ IdolListColumns.UID 
			+ "))";
	
	// 新浪微博绑定信息
	private static final String CREATE_SINA_BIND_TABLE = PRE_CREATE_TABLE + SINA_BIND_TABLE
			+ " ("
			+ SinaBindColumns.TOKEN + " TEXT PRIMARY KEY NOT NULL,"
			+ SinaBindColumns.REFRESH_TOKEN + " TEXT,"
			+ SinaBindColumns.ExpiresTime + " TEXT,"
			+ SinaBindColumns.NickName + " TEXT,"
			+ SinaBindColumns.SINA_UID + " TEXT"
			+")";

	// 鲜花留言
	private static final String CREATE_FLOWERMSG_TABLE = PRE_CREATE_TABLE + FLOWER_MSG_TABLE
			+ " ("
			+ FlowerMsgColumns.UID + " INTEGER, "
			+ FlowerMsgColumns.MSG_TEXT + " TEXT,"
			+ FlowerMsgColumns.MSG_VOICE + " TEXT,"
			+ FlowerMsgColumns.MSG_VOICE_LOCAL+ " TEXT,"
			+ FlowerMsgColumns.MSG_CONTENT_TYPE + " INTEGER,"
			+ FlowerMsgColumns.MSG_TYPE + " INTEGER,"
			+ FlowerMsgColumns.MSG_STATE + " INTEGER,"
			+ "primary key ("
			+ FlowerMsgColumns.UID + "," + FlowerMsgColumns.MSG_TYPE 
			+ "))";
	
	// 时间戳表
	private static final String CREATE_TIMESTAMP_TABLE = PRE_CREATE_TABLE + TIMESTAMP_TABLE
			+ " ("
			+ TimeStampColumns.TABLE_NAME + " TEXT PRIMARY KEY NOT NULL,"
			+ TimeStampColumns.TS + " INTEGER"
			+ ")";
	
	// 一个用户的微博列表(包含评论和赞信息,目前只需要评论信息，如果今后需要赞信息，要加数据库的赞字段)
	private static final String CREATE_USER_TOPIC_INCLUDE_NAME_TABLE = PRE_CREATE_TABLE + USER_TOPIC_INCLUDE_NAME_TABLE
			+ " ("
			+ UserTopicIncludeNamesColumns.TID + " INTEGER PRIMARY KEY NOT NULL,"
			+ UserTopicIncludeNamesColumns.TOPIC + " TEXT, "
			+ UserTopicIncludeNamesColumns.REPLYS + " TEXT, "
			+ UserTopicIncludeNamesColumns.POST_TIME + " INTEGER, "
			+ UserTopicIncludeNamesColumns.UID + " INTEGER "
			+ ")";

	// 用户最后一次定位信息表
	private static final StringBuffer CREATE_USER_LAST_LOCATION_TABLE = new StringBuffer(
			PRE_CREATE_TABLE).append(USER_LAST_LOCATION_TABLE).append(" (")
			.append(UserLastLocationColumns.CITYCODE)
			.append(" TEXT PRIMARY KEY NOT NULL,")
			.append(UserLastLocationColumns.LONGITUDE).append(" TEXT,")
			.append(UserLastLocationColumns.LATITUDE).append(" TEXT")
			.append(")");
	
	//最后一次天气信息表
	private static final StringBuffer CREATE_USER_LAST_WEATHER_TABLE = new StringBuffer(
			PRE_CREATE_TABLE).append(USER_LAST_WEATHER_TABLE).append(" (")
			.append(UserLastWeatherColumns.CITYCODE)
			.append(" TEXT PRIMARY KEY NOT NULL,")
			.append(UserLastWeatherColumns.WEATHER).append(" TEXT")
			.append(")");
		
	// 用户微博索引，uid
	public static final String CREATE_USER_TOPIC_INDEX = PRE_CREATE_INDEX 
			+ " uid_index on " + USER_TOPIC_TABLE + "("+ TopicInfoColumns.UID +")";
	
	// 话题微博索引 keyword
	private static final String CREATE_TAG_TOPIC_INDEX = PRE_CREATE_INDEX 
			+ " keyword_index on " + TAG_TOPIC_TABLE + "("+ TopicInfoColumns.KEY_WORD +")";
	
	// 评论表索引 tid
	public static final String CREATE_REPLY_INDEX = PRE_CREATE_INDEX 
			+ " tid_index on " + REPLY_TABLE + "("+ ReplyColumns.TO_TID +")";
	
	// 转发表索引 root_tid
	private static final String CREATE_RE_TOPIC_INDEX = PRE_CREATE_INDEX 
				+ " root_tid_index on " + RE_TOPIC_TABLE + "("+ TopicInfoColumns.ROOT_TID +")";
	
	// 赞某条微博用户表索引 tweet_id
	public static final String CREATE_PRAISOR_INDEX = PRE_CREATE_INDEX 
					+ " tweet_id_index on " + PRAISE_USER_TABLE + "("+ PraiseUserColumns.TWEET_ID +")";
	
	// 一个用户的微博列表索引 uid
	private static final String CREATE_USER_TOPIC_INCLUDE_NAME_INDEX = PRE_CREATE_INDEX 
			+ " uid_index on " + USER_TOPIC_INCLUDE_NAME_TABLE + "("+ UserTopicIncludeNamesColumns.UID +")";
	
	public NdWeiboDataHelper(Context context, long uid) {
		super(context, DATABASE_NAME_SUFFIX + uid + ".db3", null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.beginTransaction();
		try {
			createTableInVersion14(db);
			createTableInVersion15(db);
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.e("onCreate", e.getMessage());
		} finally {
			db.endTransaction();
		}
	}

	/**
	 * @version:14 根据新版微博改造数据库，改动较大，删除之前之前微博相关表 @huangszh
	 * @version:15增加两张天气相关的表，by:Panyun
	 * 
	 */
	private static final int VERSION = 15;

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.beginTransaction();
		try {
			if(oldVersion < 14){
				dropTableBeforeVersion14(db);
				createTableInVersion14(db);
			}
			if(oldVersion < 15){
				createTableInVersion15(db);
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.e("onUpgrade", e.getMessage());
		} finally {
			db.endTransaction();
		}
	}

	/**
	 * 
	 * @n<b>函数名称</b>     :DropTableBefore14
	 * @brief	version14 的时候重整了微博数据结构，把之前相关的表全删了，重新建
	 * @see
	 * @since    Ver 1.1
	 * @param  @param db
	 * @return void
	 * @<b>作者</b>          :  huangszh
	 * @<b>创建时间</b>      :  2014-2-14下午2:28:57
	 */
	private void dropTableBeforeVersion14(SQLiteDatabase db){
		try {
			db.execSQL("drop table if exists tweet;");
			db.execSQL("drop table if exists user_tweet;");
			db.execSQL("drop table if exists at_tweet;");
			db.execSQL("drop table if exists reply_tweet;");
			db.execSQL("drop table if exists praise_tweet;");
			db.execSQL("drop table if exists comment;");
			db.execSQL("drop table if exists user;");
			db.execSQL("drop table if exists hashtags;");
			db.execSQL("drop table if exists likeme_tweet;");
			db.execSQL("drop table if exists praise_user_list;");
			db.execSQL("drop table if exists retweet;");
			db.execSQL("drop table if exists sina_bind;");
			db.execSQL("drop table if exists black_list;");
			db.execSQL("drop table if exists attention_list;");
			
		} catch (Exception e) {
			Log.e("DropTableBefore14", e.getMessage());
		}
	}
	
	/**
	 * 
	 * @n<b>函数名称</b>     :createTableInVersion14
	 * @brief	从version14算是一个新的开始，以后的升级语句不能放在这里面，必须写在对应的UpdateTo方法里面，
	 * 			onCreate和onUpdate都调用对应的UpdateTo方法，这样逻辑比较清晰
	 * @see
	 * @since    Ver 1.1
	 * @param  @param db
	 * @return void
	 * @<b>作者</b>          :  huangszh
	 * @<b>创建时间</b>      :  2014-2-14下午2:39:11
	 */
	private void createTableInVersion14(SQLiteDatabase db){
		try {
			db.execSQL(CREATE_PUBLIC_TOPIC_TABLE);
			db.execSQL(CREATE_FRIEND_TOPIC_TABLE);
			db.execSQL(CREATE_USER_TOPIC_TABLE);
			db.execSQL(CREATE_USER_TOPIC_INDEX);
			db.execSQL(CREATE_REPLY_ME_TABLE);
			db.execSQL(CREATE_USER_TABLE);
			db.execSQL(CREATE_TAG_TOPIC_TABLE);
			db.execSQL(CREATE_TAG_TOPIC_INDEX);
			db.execSQL(CREATE_AT_TOPIC_TABLE);
			db.execSQL(CREATE_REPLY_TABLE.toString());
			db.execSQL(CREATE_REPLY_INDEX);
			db.execSQL(CREATE_PRAISE_USER_TABLE.toString());
			db.execSQL(CREATE_PRAISOR_INDEX);
			db.execSQL(CREATE_RE_TOPIC_TABLE);
			db.execSQL(CREATE_RE_TOPIC_INDEX);
			db.execSQL(CREATE_MY_PRAISED_TOPIC_TABLE);
			db.execSQL(CREATE_IDOL_LIST_TABLE);
			db.execSQL(CREATE_SINA_BIND_TABLE);
			db.execSQL(CREATE_FLOWERMSG_TABLE);
			db.execSQL(CREATE_AT_REPLY_TABLE);
			db.execSQL(CREATE_TIMESTAMP_TABLE);
			db.execSQL(CREATE_USER_TOPIC_INCLUDE_NAME_TABLE);
			db.execSQL(CREATE_USER_TOPIC_INCLUDE_NAME_INDEX);
		} catch (Exception e) {
			Log.e("createDb", e.getMessage());
		}
	}

	/**
	 * version15,新增两张天气相关的表
	 * @n<b>函数名称</b>     :createTableInVersion15
	 * @brief
	 * @see
	 * @since    Ver 1.1
	 * @param db
	 * void
	 * @<b>作者</b>          :  PanYun
	 * @<b>创建时间</b>      :  2014-4-23下午2:28:41
	 */
	private void createTableInVersion15(SQLiteDatabase db){
		try{
			db.execSQL(CREATE_USER_LAST_LOCATION_TABLE.toString());//用户最后一次定位信息表
			db.execSQL(CREATE_USER_LAST_WEATHER_TABLE.toString());//最后一次天气信息表
		}catch (Exception e) {
			Log.e("createDb", e.getMessage());
		}
	}
}
